--As of January 17, 2014 4:41:26 PM +0000, James David Smith is alleged to
have said:
> I have a table of some 100m rows of data. There are 5 columns of data.
> When I want to look at this data I typically want to sort it by ppid
> (numeric) an then by time (timestamp). This simple select can often
> take 15-20 seconds. Would adding an index to these two columns make it
> quicker? What is the best way to achieve increased speed for this
> common select query?
--As for the rest, it is mine.
An index is likely to make it faster, although I wouldn't quite want to
guarantee it. (I assume you are pulling in all the data? Otherwise an
index on your *conditions* might make sense. Sorting the data likely takes
less time than retrieving all of it.) Luckily enough it's easy to just try
it and check.
Since we are working on that one query in specific, I'd probably create an
index on those two columns, in that order: `CREATE INDEX ixd_name ON table
(ppid, time)`. I'm of course assuming that ppid's aren't unique.
But again, if you are retrieving the entire table this may not help - what
would help more is to limit the number of records you are retrieving and
put an index on that condition. (Of course, if you are using LIMIT, then
the index will definitely help.)
Daniel T. Staal
---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------